{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "file_name = \"/mnt/mydisk/pku_data/1_武汉专利协同次数统计.xlsx\"\n",
    "df = pd.read_excel(file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.environ['HTTP_PROXY'] = 'http://127.0.0.1:7890'\n",
    "os.environ['HTTPS_PROXY'] = 'http://127.0.0.1:7890'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "def save_obj(obj, name):\n",
    "    \"\"\"\n",
    "    将对象保存到文件\n",
    "    :param obj: 要保存的对象\n",
    "    :param name: 文件的名称（包括路径）\n",
    "    \"\"\"\n",
    "    with open(name, \"wb\") as f:\n",
    "        pickle.dump(obj, f, pickle.HIGHEST_PROTOCOL)\n",
    "\n",
    "\n",
    "def load_obj(name):\n",
    "    \"\"\"\n",
    "    从文件加载对象\n",
    "    :param name: 文件的名称（包括路径）\n",
    "    :return: 反序列化后的对象\n",
    "    \"\"\"\n",
    "    with open(name, \"rb\") as f:\n",
    "        return pickle.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from geopy.geocoders import Nominatim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# geolocator = Nominatim(user_agent=\"company_locator\")\n",
    "# location = geolocator.geocode(\"北京大学\")\n",
    "# print(location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_agent = \"company_locator\"\n",
    "geolocator = Nominatim(user_agent=user_agent)\n",
    "location = geolocator.geocode(\"黄鹤楼科技园有限公司\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_agent = \"company_locator\"\n",
    "geolocator = Nominatim(user_agent=user_agent)\n",
    "location = geolocator.geocode(\"清华大学\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_agent = \"company_locator\"\n",
    "geolocator = Nominatim(user_agent=user_agent)\n",
    "location = geolocator.geocode(\"鸿富锦精密工业有限公司\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Location(衡阳富士康  鸿富锦精密工业（衡阳）有限公司, 金龙坪街道, 雁峰区, 衡阳市, 湖南省, 中国, (26.820771399999998, 112.60706361808147, 0.0))"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>公司1</th>\n",
       "      <th>公司2</th>\n",
       "      <th>次数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>格力电器(武汉)有限公司</td>\n",
       "      <td>珠海格力电器股份有限公司</td>\n",
       "      <td>1419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>鸿富锦精密工业(武汉)有限公司</td>\n",
       "      <td>鸿海精密工业股份有限公司</td>\n",
       "      <td>1193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>中核武汉核电运行技术股份有限公司</td>\n",
       "      <td>核动力运行研究所</td>\n",
       "      <td>1043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>美的集团武汉制冷设备有限公司</td>\n",
       "      <td>美的集团股份有限公司</td>\n",
       "      <td>821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>武汉市黄鹤楼科技园有限公司</td>\n",
       "      <td>湖北中烟工业有限责任公司</td>\n",
       "      <td>561</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                公司1           公司2    次数\n",
       "0      格力电器(武汉)有限公司  珠海格力电器股份有限公司  1419\n",
       "1   鸿富锦精密工业(武汉)有限公司  鸿海精密工业股份有限公司  1193\n",
       "2  中核武汉核电运行技术股份有限公司      核动力运行研究所  1043\n",
       "3    美的集团武汉制冷设备有限公司    美的集团股份有限公司   821\n",
       "4     武汉市黄鹤楼科技园有限公司  湖北中烟工业有限责任公司   561"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 连接mysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from tqdm import tqdm\n",
    "import sys\n",
    "import pymysql\n",
    "\n",
    "\n",
    "sys.path.append(\"/home/jie/.key\")\n",
    "# 本地密码存储文件\n",
    "from sql_key import password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 连接到MySQL数据库\n",
    "connection = pymysql.connect(\n",
    "    host=\"localhost\",  # MySQL数据库的主机\n",
    "    user=\"root\",  # MySQL用户名\n",
    "    password=password,  # MySQL密码\n",
    "    database=\"industry\",  # 你要插入数据的数据库\n",
    "    charset=\"utf8mb4\",\n",
    "    cursorclass=pymysql.cursors.DictCursor,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_data = {\n",
    "    \"公司1\":[],\n",
    "    \"公司2\":[],\n",
    "    \n",
    "    \"公司1_经度\":[],\n",
    "    \"公司1_纬度\":[],\n",
    "    \n",
    "    \"公司2_经度\":[],\n",
    "    \"公司2_纬度\":[],\n",
    "    \n",
    "    \"次数\":[],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_address():\n",
    "    if not os.path.exists(\"address.pkl\"):\n",
    "        return {}\n",
    "    return load_obj(\"address.pkl\")\n",
    "\n",
    "\n",
    "address_d = get_address()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "address_d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_address(k, v):\n",
    "    global address_d\n",
    "    if k not in address_d.keys() or address_d.get(\"longitude\", None) is None:\n",
    "        address_d[k] = v\n",
    "    save_obj(address_d, \"address.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_company_location(company_name):\n",
    "    global address_d\n",
    "    if company_name in address_d.keys() and address_d[company_name][\"longitude\"] is not None:\n",
    "        return address_d[company_name]\n",
    "    \n",
    "    import time\n",
    "    time.sleep(1)\n",
    "    \n",
    "    try:\n",
    "        # user_agent = \"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36\"\n",
    "        user_agent = \"company_locator\"\n",
    "        geolocator = Nominatim(user_agent=user_agent)\n",
    "        location = geolocator.geocode(company_name)\n",
    "\n",
    "        if not location:\n",
    "            return None\n",
    "\n",
    "        if company_name in location.address:\n",
    "            d = {\"longitude\": location.longitude, \"latitude\": location.latitude}\n",
    "            save_address(company_name, d)\n",
    "            # address_d[company_name] = d\n",
    "            return d\n",
    "        else:\n",
    "            d = {\n",
    "                \"longitude\": None,\n",
    "                \"latitude\": None\n",
    "            }\n",
    "            save_address(company_name, d)\n",
    "            return None\n",
    "    except:\n",
    "        d = {\n",
    "                \"longitude\": None,\n",
    "                \"latitude\": None\n",
    "            }\n",
    "        save_address(company_name, d)\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'珠海格力电器股份有限公司': {'longitude': None, 'latitude': None},\n",
       " '格力电器(武汉)有限公司': {'longitude': None, 'latitude': None}}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "address_d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_company_location(\"格力电器(武汉)有限公司\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_company_location(\"珠海格力电器股份有限公司\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'珠海格力电器股份有限公司': {'longitude': None, 'latitude': None},\n",
       " '格力电器(武汉)有限公司': {'longitude': None, 'latitude': None}}"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "address_d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "address_d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "def query_data(company_name):\n",
    "    # 创建一个游标对象\n",
    "    with connection.cursor() as cursor:\n",
    "        # SQL 查询语句\n",
    "        sql = \"\"\"\n",
    "        SELECT company_name, longitude, latitude\n",
    "        FROM companies\n",
    "        WHERE company_name = %s\n",
    "        \"\"\"\n",
    "        # 执行查询\n",
    "        cursor.execute(sql, (company_name,))\n",
    "        res = cursor.fetchone()\n",
    "        \n",
    "        if res:\n",
    "            # 获取查询结果\n",
    "            d = {\n",
    "                    \"longitude\" : res[\"longitude\"],\n",
    "                    \"latitude\": res[\"latitude\"]\n",
    "                }\n",
    "            return d\n",
    "    \n",
    "    return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'longitude': Decimal('113.5502328'), 'latitude': Decimal('22.1337738')}"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_data(\"珠海格力电器股份有限公司\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def send_pipe(company_name):\n",
    "    d = query_data(connection, company_name)\n",
    "    if d:\n",
    "        return d\n",
    "\n",
    "    d = get_company_location(company_name)\n",
    "    if d:\n",
    "        return d\n",
    "\n",
    "    return {\"longitude\": None, \"latitude\": None}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "send_pipe(\"珠海格力电器股份有限公司\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "address_d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res_d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 全部有坐标信息的统计\n",
    "cnt = 0\n",
    "for _, row in tqdm(df.iterrows(), total=len(df)):\n",
    "    com1, com2 = row[\"公司1\"], row[\"公司2\"]\n",
    "    res_d = send_pipe(com1)\n",
    "    \n",
    "    com1_longitude, com1_latitude = res_d[\"longitude\"], res_d[\"latitude\"]\n",
    "    \n",
    "    \n",
    "    res_d = send_pipe(com2)\n",
    "    com2_longitude, com2_latitude = res_d[\"longitude\"], res_d[\"latitude\"]\n",
    "    \n",
    "    new_data[\"公司1\"].append(com1)\n",
    "    new_data[\"公司2\"].append(com2)\n",
    "    \n",
    "    new_data[\"公司1_经度\"].append(com1_longitude)\n",
    "    new_data[\"公司1_纬度\"].append(com1_latitude)\n",
    "    \n",
    "    new_data[\"公司2_经度\"].append(com2_longitude)\n",
    "    new_data[\"公司2_纬度\"].append(com2_latitude)\n",
    "    new_data[\"次数\"].append(row[\"次数\"])\n",
    "    \n",
    "    if com1_longitude and com2_longitude:\n",
    "        cnt += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cnt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = pd.DataFrame(new_data)\n",
    "new_df.to_excel(\"武汉专利协同经纬度_1030.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.to_csv(\"武汉专利协同经纬度.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def hi(item):\n",
    "    try:\n",
    "        return 1/item\n",
    "    except:\n",
    "        pass\n",
    "    finally:\n",
    "        print(item, \"end\")\n",
    "        return \"finally\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 end\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'finally'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hi(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "llm",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
